import datetime
import sqlite3

import cachetools

from req import LiveRoom, Device

db_file = "db.sqlite"
cache = cachetools.TTLCache(20000, 10 * 60)


# prev_time=datetime.datetime.now() # 上次插入时间
def init_table():
    with sqlite3.connect(db_file) as conn:
        cur = conn.cursor()
        cur.execute("""
        create table  if not  exists main.device
(
    id          integer
        primary key autoincrement,
    user_id     text,
    row         integer,
    box_ip      text,
    ip          text,
    status      integer,
    client_id   text,
    token    text,
    payload2    text,
    payload3    text,
    crete_time  timestamp default (datetime('now', 'localtime')),
    update_time timestamp
);
        """)
        cur.execute("""
       create table if not exists main.live_room
(
    room_id       text not null
        constraint live_room_pk
            primary key,
    room_link     text,
    gold_num      integer,
    look_time     timestamp,
    life_time_s   integer,
    crete_time    timestamp default (datetime('now', 'localtime')),
    update_time   timestamp,
    life_time_str integer
);


        """)
        conn.commit()


init_table()
print("init tables done")


def insert_device_batch(data: list):
    """批量插入设备"""
    with sqlite3.connect(db_file) as conn:
        cur = conn.cursor()
        affected_count = cur.executemany(f"""insert into device (user_id,token, row, box_ip, ip, status, client_id)
values (?,?,?,?,?,?,?);""", data).rowcount
        cur.close()
        conn.commit()
        return affected_count


def insert_or_update_device(user_id, token, update_data_: list):
    ips = tuple([d.get('ip') for d in update_data_])
    sql = "select id,ip from device where user_id = ? and ip in ({})".format(','.join(["?"] * len(ips)))
    with sqlite3.connect(db_file) as conn:
        query_values = (user_id, *ips)
        need_updated = conn.execute(sql, query_values).fetchall()
        print(need_updated)
        ip_id = {d[1]: d[0] for d in need_updated}
        if len(ip_id):
            update_values = [
                (token, d.get('row'), d.get('box_ip'), d.get('status'), d.get('client_id'), ip_id.get(d.get('ip'), '-'))
                for d in update_data_]
            rowcount = conn.executemany(
                "update device set token=?,row=?,box_ip=?,status=?,client_id=?,update_time=(datetime('now', 'localtime')) where id = ? ",
                update_values).rowcount
            print(f"update {rowcount}")
        need_insert = [
            (user_id, token, d.get('row'), d.get('box_ip'), d.get('ip'), d.get('status'), d.get('client_id'),)
            for d in update_data_ if d.get('ip') not in ip_id.keys()]
        # rowcount = insert_device_batch(need_insert)
        rowcount = conn.executemany(f"""insert into device (user_id,token, row, box_ip, ip, status, client_id)
        values (?,?,?,?,?,?,?);""", need_insert).rowcount
        print(f"insert {rowcount}")


def get_idle_devices(count: int) -> list[Device]:
    with sqlite3.connect(db_file) as conn:
        # placeholders = ','.join(['?'] * len(device_ids))
        sql = "select id,token,client_id,ip,box_ip from device where status=0 and token != 'no-token' limit ?"
        print(f"sql={sql}")
        r = conn.execute(sql, (count,))
        fetchall = r.fetchall()
        # print(fetchall)
        return [Device(id=r[0], token=r[1], client_id=r[2], ip=r[3], box_ip=r[4]) for r in fetchall]
        # conn.close()


def update_device_status(ids: tuple, status: int, token) -> int:
    """
    :param ids: 设备ID
    :param status: 状态吗 0空闲 1工作中 10调度中
    :return: 影响的行数
    """
    with sqlite3.connect(db_file) as conn:
        ph = ','.join(["?"] * len(ids))
        sql = "update device set status={} where token='{}' and id in ({})".format(status,token, ph)
        print(f"[update_device_status]debug sql={sql}")
        return conn.execute(sql, ids).rowcount


def insert_live_room_batch(data: list):
    """批量插入直播间"""
    with sqlite3.connect(db_file) as con:
        cur = con.cursor()
        cur.executemany(
            "insert into main.live_room (room_id, room_link, url_scheme,gold_num, look_time, life_time_s, life_time_str,expire_time) values (?,?,?,?,?,?,?,?);",
            data)
        con.commit()


def insert_or_update_live_room(d: LiveRoom):
    """插入或更新单个直播间"""
    with sqlite3.connect(db_file) as con:
        split = d.bag_rem_time.split(":")
        life_time_s = int(split[0]) * 60 + int(split[1])
        cur = con.cursor()
        count = cur.execute("select count(room_id) from live_room where room_id = %s" % d.room_id).fetchone()
        if count[0] > 0:
            cur.execute(
                """update live_room set gold_num = ?,look_time=?,life_time_s=?,life_time_str=?,update_time=?,
                room_link=?,url_scheme=?,expire_time=? where room_id=?""",
                (
                    d.gold_num, d.datatime, life_time_s, d.bag_rem_time, datetime.datetime.now(), d.room_link,
                    d.scheme_url,
                    d.expire_time,
                    d.room_id))
        else:
            # room_id, room_link, room_link,，gold_num, look_time, life_time_s, life_time_str
            insert_live_room_batch(
                [(d.room_id,
                  d.room_link,
                  d.scheme_url,
                  d.gold_num,
                  d.datatime,
                  life_time_s,
                  d.bag_rem_time,
                  d.expire_time)]
            )
        con.commit()


def insert_or_update_live_room_batch(data_list: list[LiveRoom]):
    """批量插入或更新直播间"""
    le = len(data_list)
    if le > 10000:
        print(f"数据条数太多({le})，最多10000，过多的数据将丢弃，或考虑分批处理")
    data_list = data_list[0:10000]
    room_ids = {room.room_id: room for room in data_list}
    to_add_list = []
    print(f"items = {len(data_list)}")
    with sqlite3.connect(db_file) as con:
        cur = con.cursor()
        to_updates = [d[0] for d in cur.execute(
            "select room_id from live_room where room_id in(%s)" % ','.join(room_ids.keys())).fetchall()]
        # print(to_updates)
        for room_id, room in room_ids.items():
            # print("k="+k)
            split = room.bag_rem_time.split(":")
            life_time_s = int(split[0]) * 60 + int(split[1])
            if room_id in to_updates:
                cur.execute(
                    """update live_room set gold_num = ?,look_time=?,life_time_s=?,life_time_str=?,update_time=?,
                    room_link=?,url_scheme=?,expire_time=? where room_id=?""",
                    (room.gold_num, room.datatime, life_time_s, room.bag_rem_time, datetime.datetime.now(),
                     room.room_link,
                     room.scheme_url, room.expire_time, room.room_id))
            else:
                to_add_list.append(room)
        con.commit()

        # print(f"insert batch... new list={len(to_add_list)}")
        insert_live_room_batch(
            [(d.room_id, d.room_link, d.scheme_url, d.gold_num, d.datatime, life_time_s, d.bag_rem_time, d.expire_time)
             for d in
             to_add_list])
        # con.commit()
